﻿Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
<System.Web.Script.Services.ScriptService()> _
Public Class AutoComplete_IPORT
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
        Return "Hello World"
    End Function

    <WebMethod()> _
    Public Function GetVessel(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT VEHICLE + '-' + VEH_TEXT as VEH_TEXT From Vessel "
        If (prefixText <> "") Then
            sql &= " WHERE ((VEH_TEXT Like '%' + @VEH_TEXT + '%')OR(VEHICLE Like '%' + @VEH_TEXT + '%'))"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@VEH_TEXT", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.Fill(ds, "Vessel")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("VEH_TEXT").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetTug(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Tug_Name From Tug "
        If (prefixText <> "") Then
            sql &= " WHERE (Tug_Name Like '%' + @Tug_Name + '%')"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Tug_Name", SqlDbType.NVarChar, 200).Value = prefixText
        End If
        adap.Fill(ds, "Tug_Name")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Tug_Name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetBas(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Bas_name From Bas_Master "
        If (prefixText <> "") Then
            sql &= " WHERE (Bas_name Like '%' + @Bas_name + '%')"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Bas_name", SqlDbType.NVarChar, 200).Value = prefixText
        End If
        adap.Fill(ds, "Bas_name")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Bas_name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetCustomer(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Distinct Customer.Customer_no + '-' + Customer.NAME1 + ' ' + Customer.NAME2 + ' ' + Customer.Name3 as Name From Customer,Customer_Billto "
        If (prefixText <> "") Then
            sql &= " WHERE (((Customer.Name1 + Customer.Name2 + Customer.Name3) Like '%' + @Name + '%')OR(Customer. Customer_no Like '%' + @Name + '%'))"
            sql &= " AND Customer.Customer_no = Customer_Billto.Customer_no "
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.Fill(ds, "Customer")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetProduct(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Product_name From Product,Product_Group WHERE Product_no <> '' "
        sql &= " AND Product_Group.Group_PORT = @PORT "
        sql &= " AND Product_Group.Group_no = Product.Group_no "
        If (prefixText <> "") Then
            sql &= " AND Product.Product_name Like '%' + @Product_name + '%'"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Product_name", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.SelectCommand.Parameters.Add("@PORT", SqlDbType.NVarChar, 1).Value = contextKey
        adap.Fill(ds, "Customer")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Product_name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetGroupProduct(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Group_name From Product_Group WHERE Group_no <> '' "
        If (prefixText <> "") Then
            sql &= " AND Group_name Like '%' + @Group_name + '%'"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Group_name", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.Fill(ds, "Group")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Group_name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetFacility(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Mla_Host + '-' + Diameter as Facility_Name From Facility WHERE Facility_Code <> '' "
        If (prefixText <> "") Then
            sql &= " AND (Mla_Host Like '%' + @Mla_Host + '%') OR (Diameter Like '%' + @Mla_Host + '%') "
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Mla_Host", SqlDbType.NVarChar, 400).Value = prefixText
        End If
        adap.Fill(ds, "Facility")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Facility_Name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function GetBillTo(ByVal prefixText As String, ByVal count As Integer, ByVal contextKey As String) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Bill_to + '-' + NAME1 + ' ' + NAME2 + ' ' + Name3 as Name From Customer_Billto "
        sql &= " WHERE Customer_no = @Customer "
        If (prefixText <> "") Then
            sql &= " AND (((Name1 + Name2 + Name3) Like '%' + @Name + '%')OR(Bill_to Like '%' + @Name + '%'))"
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.SelectCommand.Parameters.Add("@Customer", SqlDbType.NVarChar, 10).Value = contextKey
        adap.Fill(ds, "CustomerBillTo")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Name").ToString())
        Next
        Return item.ToArray()
    End Function

    <WebMethod()> _
Public Function CauseOfDelay(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As New System.Data.SqlClient.SqlConnection(connString)
        Dim adap As New System.Data.SqlClient.SqlDataAdapter()
        Dim sql As String = ""
        Dim ds As New DataSet
        sql = " SELECT Cause_Delay "
        sql &= " FROM CauseOfDelay "
        If (prefixText <> "") Then
            sql &= " WHERE Cause_Delay Like '%' + @Name + '%' "
        End If
        adap.SelectCommand = New System.Data.SqlClient.SqlCommand(sql, conn)
        adap.SelectCommand.Parameters.Clear()
        If (prefixText <> "") Then
            adap.SelectCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = prefixText
        End If
        adap.Fill(ds, "CauseOfDelay")
        adap = Nothing
        conn.Close()
        Dim item As New List(Of String)(count)
        For Each dr As DataRow In ds.Tables(0).Rows
            item.Add(dr("Cause_Delay").ToString())
        Next
        Return item.ToArray()
    End Function

End Class